package com.bonc.ioc.service.impl;

import cn.hutool.core.bean.BeanUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.bonc.ioc.base.page.PageData;
import com.bonc.ioc.base.page.PageResult;
import com.bonc.ioc.base.service.BaseService;
import com.bonc.ioc.common.util.AppReply;
import com.bonc.ioc.dao.StandardBasicDataMapper;
import com.bonc.ioc.dao.StandardBasicDataOptionMapper;
import com.bonc.ioc.dao.StandardDetailMapper;
import com.bonc.ioc.dao.StandardMapper;
import com.bonc.ioc.service.StandardService;
import com.bonc.ioc.service.SysDicInfoService;
import com.bonc.ioc.util.*;
import com.bonc.ioc.util.DateUtil;
import com.bonc.ioc.vo.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.http.util.EntityUtils;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.elasticsearch.action.search.SearchRequest;
import org.elasticsearch.action.search.SearchResponse;
import org.elasticsearch.client.Request;
import org.elasticsearch.client.Response;
import org.elasticsearch.client.RestHighLevelClient;
import org.elasticsearch.common.text.Text;
import org.elasticsearch.index.query.BoolQueryBuilder;
import org.elasticsearch.index.query.QueryBuilders;
import org.elasticsearch.search.SearchHit;
import org.elasticsearch.search.SearchHits;
import org.elasticsearch.search.builder.SearchSourceBuilder;
import org.elasticsearch.search.fetch.subphase.highlight.HighlightField;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.validation.BindException;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.sql.Connection;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

import static com.bonc.ioc.service.impl.EsServiceImpl.COMMON_OPTIONS;

/**
 * @version 1.0
 * @description:
 * @author: wangqinglin
 * @date: 2021/3/18 11:06
 * @change : 2021/3/18 11:06 by q8541q@163.com  for init
 */
@Service
@Slf4j
public class StandardServiceImpl extends BaseService implements StandardService {

    @Resource
    private StandardMapper standardMapper;

    @Autowired
    private HttpServletRequest request;

    @Autowired
    private BackUpDataBaseManager back;

    @Autowired
    private HttpServletResponse response;

    @Autowired
    private SysDicInfoService dicService;

    @Autowired
    private StandardBasicDataMapper standardBasicDataMapper;
    @Autowired
    private StandardBasicDataOptionMapper standardBasicDataOptionMapper;

    @Autowired
    private StandardDetailMapper standardDetailMapper;
    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    @Autowired
    public RestHighLevelClient client;
    @Autowired
    public StandardDetailServiceImpl standardDetailService;

    @Override
    public int deleteByPrimaryKey(String id) {
        return standardMapper.delete(id);
    }

    @Override
    public int save(StandardVo record) {
        if (record.getId() != null) {
            fillModifyTimeInfo(record);
            return standardMapper.update(record);
        } else {
            fillBasicTimeInfo(record);
            fillModifyTimeInfo(record);
            int save = standardMapper.save(record);
            List<SysDicInfoVo> sysDicInfoVos = getSysDicInfoVos(record);
            int size = sysDicInfoVos.size();
            for (int i = 1; i <= size; i++) {
                StandardBasicDataVo vo = new StandardBasicDataVo();
                vo.setName(sysDicInfoVos.get(i - 1).getDicName());
                vo.setStId(record.getId());
                vo.setType("3f6d197565c4490fa6b3f2c65de024fe");
                vo.setLimits("-");
                vo.setSort(((-size) + i));
                vo.setCreateTime(new Date());
                vo.setModifyTime(new Date());
                standardBasicDataMapper.save(vo);
            }
            return save;
        }
    }

    @Override
    public StandardVo selectByPrimaryKey(Long id) {

        StandardVo standardVo = standardMapper.selectByPrimaryKey(id);
        standardVo.setVisitNum(standardVo.getVisitNum() + 1);
        standardMapper.update(standardVo);
        return standardVo;
    }

    @Override
    public PageResult selectStandardInfoByPage(PageData pageData) throws BindException {
        getPageParams();
        PageResult pageResult = new PageResult(pageData, standardMapper.selectByPage(pageData));
        return pageResult;
    }


    private void fillModifyTimeInfo(StandardVo record) {
        record.setModifyTime(new Date());
    }

    private void fillBasicTimeInfo(StandardVo record) {
        record.setCreateTime(new Date());
    }

    @Override
    public PageResult selectStandardInfoShowByPage(PageData pageData, String sjbs) throws BindException {
        getPageParams();
        pageData.put("sjbs", sjbs);
        PageResult pageResult = new PageResult(pageData, standardMapper.selectStandardInfoShowByPage(pageData));
        return pageResult;
    }

    @Override
    public List<StandardVo> queryStandRanking(Integer id) {
        List<StandardVo> list = standardMapper.queryStandRanking(id);
        return list;
    }

    @Override
    public void downloadStandModel(String id) throws IOException {

        // 设置导出的编码格式，此处统一为UTF-8
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        // 设置导出文件的名称
        response.setHeader("Content-Disposition",
                "attachment;filename=" + new String("标准导入模板.xls".getBytes(), "iso-8859-1"));
        // 模拟表格需要导出的数据
        //List<SysDicInfoVo> dicInfoVos = getSysDicInfoVos(id);
        List<StandardBasicDataVo> dicInfoVoss = getStandardBasicDataVos(id);
        String title = "";
//        for (SysDicInfoVo dicInfoVo : dicInfoVos) {
//            title += "," + dicInfoVo.getDicName();
//        }
        for (StandardBasicDataVo dicInfoVo : dicInfoVoss) {
            title += "," + dicInfoVo.getName();
        }
        if (StringUtils.isBlank(title)) {
            return;
        }
        //List<ExportInfo> exportInfos = managementServicesActivitySignMapper.findAllRecords(activityId, dbConfig.getAppDb(), dbConfig.getOrgDb());
        // 实际应用中这个地方会判断获取的数据，如果没有对应的数据则不导出，如果超过2000条，则只导出2000条
        ServletOutputStream out = response.getOutputStream();
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            ExportUtil dataExportUtil = new ExportUtil();
            bis = new BufferedInputStream(dataExportUtil.getExcelStream(title.substring(1)));
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
            bos.flush();
        } catch (final IOException e) {
            System.out.println("数据导出列表导出异常！");
        } finally {
            if (bis != null) {
                bis.close();
            }
            if (bos != null) {
                bos.close();
            }
        }
    }

    private List<SysDicInfoVo> getSysDicInfoVos(String id) {
        List<SysDicInfoVo> infoVos = getDefaultFiledByStandType(id);
        List<SysDicInfoVo> dicInfoVos = infoVos.stream().sorted(Comparator.comparing(SysDicInfoVo::getDicOrder)).collect(Collectors.toList());
        return dicInfoVos;
    }

    private List<StandardBasicDataVo> getStandardBasicDataVos(String id) {
        List<StandardBasicDataVo> infoList = standardBasicDataMapper.getBasicFieldInfoList(id);
        List<StandardBasicDataVo> dicInfoVoss = infoList.stream().sorted(Comparator.comparing(StandardBasicDataVo::getSort)).collect(Collectors.toList());
        return dicInfoVoss;
    }

    public List<SysDicInfoVo> getDefaultFiledByStandType(String id) {
        StandardVo standardVo = standardMapper.selectByPrimaryKey(Long.valueOf(id));
        return getSysDicInfoVos(standardVo);
    }

    private List<SysDicInfoVo> getSysDicInfoVos(StandardVo standardVo) {
        List<SysDicInfoVo> serviceByType = dicService.getByType(DictionaryEnum.STANDARDTYPE.getValue());
        String defaultTypeFiled = "";
        for (SysDicInfoVo dicInfoVo : serviceByType) {
            if (dicInfoVo.getDicCode().equalsIgnoreCase(standardVo.getType())) {
                defaultTypeFiled = dicInfoVo.getExtend1();
            }
        }
        if (StringUtils.isBlank(defaultTypeFiled)) {
            return null;
        }
        List<SysDicInfoVo> infoVos = dicService.getByType(defaultTypeFiled);
        return infoVos;
    }

    @Override
    public List<StandardVo> getAssociationInfo(String query) throws IOException {
        Set<String> strings = new LinkedHashSet<>();
        Long l = -1L;
        if (StringUtils.isBlank(query)) {
            List<StandardVo> standardVos = queryStandRanking(1);
            if (standardVos.size() <= 0) {
                return null;
            }
            LinkedList<StandardVo> list = new LinkedList<>();
            list.add(standardVos.get(0));
            strings = getParticipleByES(standardVos.get(0).getTile());
            log.info(strings.toString() + "5555555555555555555");
            l = standardVos.get(0).getId();
        } else {
            strings = getParticipleByES(query);
        }
        return searchAssociationStandByKeys(strings, l);
        //  return queryStandRanking(3);
    }

    @Override
    public PageResult getAssociationInfo2(PageData pageData) {
        HashMap<String, Object> map = new HashMap<>();
        //拼接es搜索语句
        SearchSourceBuilder searchSourceBuilder = buildSearchSource((String) pageData.get("query"));

        //创建并设置SearchRequest对象
        SearchRequest searchRequest = new SearchRequest();
        //设置request要搜索的索引和类型
        searchRequest.indices("stand_title");
        //设置SearchSourceBuilder查询属性
        searchRequest.source(searchSourceBuilder);
        SearchResponse searchResponse = null;
        try {
            searchResponse = client.search(searchRequest, COMMON_OPTIONS);
        } catch (IOException e) {
            e.printStackTrace();
        }
        assert searchResponse != null;
        SearchHits hits1 = searchResponse.getHits();
        System.out.println("-------" + hits1.getTotalHits());
        SearchHit[] hits = searchResponse.getHits().getHits();
        log.error("999999------" + hits.length);
        List<StandardVo> standardVoList = new ArrayList<>();
        Arrays.stream(hits).forEach(hit -> {
            Map<String, Object> sourceAsMap = hit.getSourceAsMap();
            //遍历高亮结果
            Map<String, HighlightField> highlightFields = hit.getHighlightFields();
            log.info("数据与规范 高亮显示结果 高亮部分={}", JSON.toJSONString(highlightFields));
            List<String> keyList = JsonUtils.getFirstLevelKeyList(JSON.toJSONString(highlightFields));
            log.info("数据与规范 高亮显示结果 高亮部分keyList={}", keyList);
            for (String key : keyList) {
                HighlightField highlight = highlightFields.get(key);
                if (highlight != null) {
                    Text[] fragments = highlight.getFragments();
                    StringBuilder value = new StringBuilder();
                    for (Text text : fragments) {
                        value.append(text);
                    }
                    sourceAsMap.put(key, value.toString());
                }
            }
            log.info("数据资产 ES检索结果={}", sourceAsMap);
            StandardVo standardVo = BeanUtil.mapToBeanIgnoreCase(sourceAsMap, StandardVo.class, true);
            standardVoList.add(standardVo);
        });
        pageData.put("totalRows", "1");
        PageResult pageResult = new PageResult(pageData, standardVoList);
        return pageResult;
    }


    @Override
    public List<StandardVo> getAssociationInfo3(String query) {
        HashMap<String, Object> map = new HashMap<>();
        //拼接es搜索语句
        SearchSourceBuilder searchSourceBuilder = buildSearchSource(query);

        //创建并设置SearchRequest对象
        SearchRequest searchRequest = new SearchRequest();
        //设置request要搜索的索引和类型
        searchRequest.indices("stand_title");
        //设置SearchSourceBuilder查询属性
        searchRequest.source(searchSourceBuilder);
        SearchResponse searchResponse = null;
        try {
            searchResponse = client.search(searchRequest, COMMON_OPTIONS);
        } catch (IOException e) {
            e.printStackTrace();
        }
        assert searchResponse != null;
        SearchHits hits1 = searchResponse.getHits();
        System.out.println("-------" + hits1.getTotalHits());
        SearchHit[] hits = searchResponse.getHits().getHits();
        log.error("999999------" + hits.length);
        List<StandardVo> standardVoList = new ArrayList<>();
        Arrays.stream(hits).forEach(hit -> {
            Map<String, Object> sourceAsMap = hit.getSourceAsMap();
            //遍历高亮结果
            Map<String, HighlightField> highlightFields = hit.getHighlightFields();
            log.info("数据与规范 高亮显示结果 高亮部分={}", JSON.toJSONString(highlightFields));
            List<String> keyList = JsonUtils.getFirstLevelKeyList(JSON.toJSONString(highlightFields));
            log.info("数据与规范 高亮显示结果 高亮部分keyList={}", keyList);
            for (String key : keyList) {
                HighlightField highlight = highlightFields.get(key);
                if (highlight != null) {
                    Text[] fragments = highlight.getFragments();
                    StringBuilder value = new StringBuilder();
                    for (Text text : fragments) {
                        value.append(text);
                    }
                    sourceAsMap.put(key, value.toString());
                }
            }
            log.info("数据资产 ES检索结果={}", sourceAsMap);
            StandardVo standardVo = BeanUtil.mapToBeanIgnoreCase(sourceAsMap, StandardVo.class, true);

            standardVoList.add(standardVo);
        });
        return standardVoList;
    }


    @Override
    public String importBasicInfoByExcel(List<List<String>> lists, String id, String basicId) {
        String[] strings = basicId.split(",");
        LinkedList<StandardDetailVo> linkedList = new LinkedList<>();
        for (int i = 1; i < lists.size(); i++) {
            List<String> stringList = lists.get(i);
            //每行的recordId都是不一样的
            String uuid = UUID.randomUUID().toString();
            for (int ii = 0; ii < strings.length; ii++) {
                StandardDetailVo detailVo = new StandardDetailVo();
                detailVo.setContent(stringList.get(ii));
                detailVo.setBdId(Long.valueOf(strings[ii]));
                detailVo.setRecordId(uuid);
                linkedList.add(detailVo);
            }
        }
        SaveStandardDetailVo vo = new SaveStandardDetailVo();
        vo.setStId(Long.valueOf(id));
        vo.setKv(linkedList);
        standardDetailService.save(vo);
        return "导入成功";
    }

    @Override
    public void downloadStandModelAll(HttpServletResponse response) {
        OutputStream os = null;
        XSSFWorkbook wb = null;
        try {
            wb = new XSSFWorkbook();

            //生成standard的数据
            List<String> standardColumns = standardMapper.selectStandardColumns();//查询standard字段名
            //List<Map<String, Object>> standardList = standardMapper.selectAll(); // 查询所有

            XSSFSheet sheet = wb.createSheet("standard");
            XSSFRow row = sheet.createRow(0);
            row.setHeight((short) (22.5 * 20));
            for (int i = 1; i < standardColumns.size(); i++) {
                if (i == 1 || i == 5 || i == 7 || i == 10 || i == 18 || i == 20 || i == 21 || i == 23 || i == 26 || i == 27 || i == 29)
                    row.createCell(i - 1).setCellValue(standardColumns.get(i) + "*");
                else row.createCell(i - 1).setCellValue(standardColumns.get(i));
            }
          /*  for (int i = 0; i < standardList.size(); i++) {
                XSSFRow row1 = sheet.createRow(i + 1);

                for (int j = 0; j < standardColumns.size(); j++) {
                    if (standardList.get(i).get(standardColumns.get(j)) == null) {
                        row1.createCell(j).setCellValue("");
                    } else row1.createCell(j).setCellValue(standardList.get(i).get(standardColumns.get(j)).toString());
                }
            }*/
          /*  //StandardBasicData
            List<String> standardBasicDataColumns = standardMapper.selectStandardBasicDataColumns();//查询standard_basic_data字段名
            List<Map<String, Object>> standardBasicDataList = standardMapper.selectStandardBasicDataAll(); // 查询所有

            XSSFSheet standardBasicDataSheet = wb.createSheet("standard_basic_data");
            XSSFRow standardBasicDatarow = standardBasicDataSheet.createRow(0);
            standardBasicDatarow.setHeight((short) (22.5 * 20));
            for (int i = 0; i < standardBasicDataColumns.size(); i++) {
                standardBasicDatarow.createCell(i).setCellValue(standardBasicDataColumns.get(i));
            }
            for (int i = 0; i < standardBasicDataList.size(); i++) {
                XSSFRow row1 = standardBasicDataSheet.createRow(i + 1);

                for (int j = 0; j < standardBasicDataColumns.size(); j++) {
                    if (standardBasicDataList.get(i).get(standardBasicDataColumns.get(j)) == null) {
                        row1.createCell(j).setCellValue("");
                    } else
                        row1.createCell(j).setCellValue(standardBasicDataList.get(i).get(standardBasicDataColumns.get(j)).toString());
                }
            }

            //standardbasic_data_option
            //standardbasicDataOption
            List<String> standardbasicDataOptionColumns = standardMapper.selectStandardBasicDataOptionColumns();//standardbasic_data_option
            List<Map<String, Object>> standardbasicDataOptionDataList = standardMapper.selectStandardBasicOptionDataAll(); // 查询所有

            XSSFSheet standardBasicDataOptionSheet = wb.createSheet("standard_basic_data_option");
            XSSFRow standardBasicDataOptionrow = standardBasicDataOptionSheet.createRow(0);
            standardBasicDataOptionrow.setHeight((short) (22.5 * 20));
            for (int i = 0; i < standardbasicDataOptionColumns.size(); i++) {
                standardBasicDataOptionrow.createCell(i).setCellValue(standardbasicDataOptionColumns.get(i));
            }
            for (int i = 0; i < standardbasicDataOptionDataList.size(); i++) {
                XSSFRow row1 = standardBasicDataOptionSheet.createRow(i + 1);

                for (int j = 0; j < standardbasicDataOptionColumns.size(); j++) {
                    if (standardbasicDataOptionDataList.get(i).get(standardbasicDataOptionColumns.get(j)) == null) {
                        row1.createCell(j).setCellValue("");
                    } else
                        row1.createCell(j).setCellValue(standardbasicDataOptionDataList.get(i).get(standardbasicDataOptionColumns.get(j)).toString());
                }
            }

            //standard_detail
            //standardDetail
            List<String> standardDetailColumns = standardMapper.selectStandardDetailColumns();//standard_detail
            List<Map<String, Object>> standardDetailDataList = standardMapper.selectStandardDetailAll(); // 查询所有

            XSSFSheet standardDetailSheet = wb.createSheet("standard_detail");
            XSSFRow standardDetailrow = standardDetailSheet.createRow(0);
            standardDetailrow.setHeight((short) (22.5 * 20));
            for (int i = 0; i < standardDetailColumns.size(); i++) {
                standardDetailrow.createCell(i).setCellValue(standardDetailColumns.get(i));
            }
            for (int i = 0; i < standardDetailDataList.size(); i++) {
                XSSFRow row1 = standardDetailSheet.createRow(i + 1);

                for (int j = 0; j < standardDetailColumns.size(); j++) {
                    if (standardDetailDataList.get(i).get(standardDetailColumns.get(j)) == null) {
                        row1.createCell(j).setCellValue("");
                    } else
                        row1.createCell(j).setCellValue(standardDetailDataList.get(i).get(standardDetailColumns.get(j)).toString());
                }
            }*/

            response.setContentType("application/octet-stream;charset=utf-8");
            os = response.getOutputStream();
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode("标准列表模板", "UTF-8") + ".xlsx");
            wb.write(os);
            os.flush();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (wb != null) {
                try {
                    wb.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public AppReply importExcelAll(MultipartFile file) {
        String fileName = file.getOriginalFilename();
        log.info("fileName=" + fileName);
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            return AppReply.success("上传文件格式不正确");
        }


        try {
            AppReply appReply = importExcelAllFileByStandardVo(file);
//            importExcelAllFileByStandardBasicData(file);
//            importExcelAllFileByStandardBasicDataOption(file);
//            importExcelAllFileByStandardDetail(file);
            return appReply;
        } catch (Exception e) {
            return AppReply.error("批量插入失败");
        }
    }

    @Override
    public void downloadSql(HttpServletResponse response) {
        String outPath = System.getProperty("user.dir") + "/";
        String fileName = "standard.sql";
        back.exportSql(outPath, fileName);
        OutputStream os = null;
        FileInputStream ips = null;
        try {
            response.setContentType("application/octet-stream;charset=utf-8");
            os = response.getOutputStream();
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode("标准列表", "UTF-8") + DateUtil.dateToStr(new Date(), "yyyyMMddHHmmss") + ".sql");
            ips = new FileInputStream(outPath + fileName);
            int len = 0;
            byte[] buffer = new byte[1024 * 10];
            while ((len = ips.read(buffer)) != -1) {
                os.write(buffer, 0, len);
            }
            os.flush();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (ips != null) {
                try {
                    ips.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            File file = new File(outPath + fileName);
            if (file.exists()) {
                file.delete();
            }
        }


    }

    @Override
    public void importSql(MultipartFile file) throws IOException {

        SqlSession sqlSession = sqlSessionFactory.openSession();
        Connection conn = sqlSession.getConnection();
        ScriptRunner runner = new ScriptRunner(conn);
        runner.setEscapeProcessing(false);
        runner.setSendFullScript(true);
        runner.runScript(new InputStreamReader(file.getInputStream()));

    }

    @Transactional
    public AppReply importExcelAllFileByStandardVo(MultipartFile file) {
        //standardMapper.deleteAll();
        Workbook workbook = null;
        try {
            InputStream is = file.getInputStream();
            workbook = WorkbookFactory.create(is);
            Sheet standardSheet = workbook.getSheet("standard");
            ArrayList<String> standardKey = new ArrayList<>();
            //获取属性名row
            Row titleRow = standardSheet.getRow(0);
            for (int i = 0; i < titleRow.getLastCellNum(); i++) {

                standardKey.add(titleRow.getCell(i).getStringCellValue().replace("*", ""));
            }
            for (int i = 1; i < standardSheet.getPhysicalNumberOfRows(); i++) {
                //获取每一行的数据
                Row standardRow = standardSheet.getRow(i);
                //包含bean属性名和value的json
                JSONObject beanJson = new JSONObject();
                //读取到数据没有数据的时候，结束读取
                if (standardRow == null) {
                    break;
                }
                for (int j = 0; j < standardKey.size(); j++) {
                    //将属性名和数据放在json里


                    Cell cell = standardRow.getCell(j);
                    if (cell == null) {
                        beanJson.put(standardKey.get(j), "");
                    } else {
                        cell.setCellType(CellType.STRING);
                        beanJson.put(standardKey.get(j), cell.getStringCellValue());
                    }

                }
                //excel中的状态码和对应的字段进行解析 SEX_M:男转为SEX_M
                //修改对应字段的属性，比如2018/12/25修改为date型数据
                beanJson = reInitBeanJson(beanJson);

                StandardVo standardVo = new StandardVo();
                //转换为对应的实体类
                standardVo = beanJson.toJavaObject(StandardVo.class);

                standardMapper.saveAll(standardVo);

            }

        } catch (Exception e) {
            log.error(String.format("parse excel exception!"), e);
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    log.error(String.format("parse excel exception!"), e);
                }
            }
        }
        return AppReply.error("添加成功");
    }

  /*  @Transactional
    public void importExcelAllFileByStandardBasicData(MultipartFile file) {
        standardBasicDataMapper.deleteAll();
        Workbook workbook = null;
        try {
            InputStream is = file.getInputStream();
            workbook = WorkbookFactory.create(is);
            Sheet standardSheet = workbook.getSheet("standard_basic_data");
            ArrayList<String> standardKey = new ArrayList<>();
            //获取属性名row
            Row titleRow = standardSheet.getRow(0);
            for (int i = 0; i < titleRow.getLastCellNum(); i++) {
                standardKey.add(titleRow.getCell(i).getStringCellValue());
            }
            for (int i = 1; i < standardSheet.getPhysicalNumberOfRows(); i++) {
                //获取每一行的数据
                Row standardRow = standardSheet.getRow(i);
                //包含bean属性名和value的json
                JSONObject beanJson = new JSONObject();
                //读取到数据没有数据的时候，结束读取
                if (standardRow == null) {
                    break;
                }
                for (int j = 0; j < standardKey.size(); j++) {
                    //将属性名和数据放在json里
                    beanJson.put(standardKey.get(j), standardRow.getCell(j).getStringCellValue());
                }
                //excel中的状态码和对应的字段进行解析 SEX_M:男转为SEX_M
                //修改对应字段的属性，比如2018/12/25修改为date型数据
                beanJson = reInitBeanJson(beanJson);

                StandardBasicDataVo standardBasicDataVo = new StandardBasicDataVo();
                //转换为对应的实体类
                standardBasicDataVo = beanJson.toJavaObject(StandardBasicDataVo.class);
                standardBasicDataMapper.saveAll(standardBasicDataVo);
            }

        } catch (Exception e) {
            log.error(String.format("parse excel exception!"), e);
            return AppReply.error("添加失败");
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    log.error(String.format("parse excel exception!"), e);
                }
            }
        }
    }

    //standard_basic_data_option
    //StandardBasicDataOption
    @Transactional
    public void importExcelAllFileByStandardBasicDataOption(MultipartFile file) {
        standardBasicDataOptionMapper.deleteAll();
        Workbook workbook = null;
        try {
            InputStream is = file.getInputStream();
            workbook = WorkbookFactory.create(is);
            Sheet standardSheet = workbook.getSheet("standard_basic_data_option");
            ArrayList<String> standardKey = new ArrayList<>();
            //获取属性名row
            Row titleRow = standardSheet.getRow(0);
            for (int i = 0; i < titleRow.getLastCellNum(); i++) {
                standardKey.add(titleRow.getCell(i).getStringCellValue());
            }
            for (int i = 1; i < standardSheet.getPhysicalNumberOfRows(); i++) {
                //获取每一行的数据
                Row standardRow = standardSheet.getRow(i);
                //包含bean属性名和value的json
                JSONObject beanJson = new JSONObject();
                //读取到数据没有数据的时候，结束读取
                if (standardRow == null) {
                    break;
                }
                for (int j = 0; j < standardKey.size(); j++) {
                    //将属性名和数据放在json里
                    beanJson.put(standardKey.get(j), standardRow.getCell(j).getStringCellValue());
                }
                //excel中的状态码和对应的字段进行解析 SEX_M:男转为SEX_M
                //修改对应字段的属性，比如2018/12/25修改为date型数据
                beanJson = reInitBeanJson(beanJson);
                StandardBasicDataOptionVo standardBasicDataOption = new StandardBasicDataOptionVo();
                //转换为对应的实体类
                standardBasicDataOption = beanJson.toJavaObject(StandardBasicDataOptionVo.class);
                standardBasicDataOptionMapper.saveAll(standardBasicDataOption);
            }

        } catch (Exception e) {
            log.error(String.format("parse excel exception!"), e);
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    log.error(String.format("parse excel exception!"), e);
                }
            }
        }
    }

    //standard_detail
    //StandardDetail
    @Transactional
    public void importExcelAllFileByStandardDetail(MultipartFile file) {
        standardDetailMapper.deleteAll();
        Workbook workbook = null;
        try {
            InputStream is = file.getInputStream();
            workbook = WorkbookFactory.create(is);
            Sheet standardSheet = workbook.getSheet("standard_detail");
            ArrayList<String> standardKey = new ArrayList<>();
            //获取属性名row
            Row titleRow = standardSheet.getRow(0);
            for (int i = 0; i < titleRow.getLastCellNum(); i++) {
                standardKey.add(titleRow.getCell(i).getStringCellValue());
            }
            for (int i = 1; i < standardSheet.getPhysicalNumberOfRows(); i++) {
                //获取每一行的数据
                Row standardRow = standardSheet.getRow(i);
                //包含bean属性名和value的json
                JSONObject beanJson = new JSONObject();
                //读取到数据没有数据的时候，结束读取
                if (standardRow == null) {
                    break;
                }
                for (int j = 0; j < standardKey.size(); j++) {
                    //将属性名和数据放在json里
                    beanJson.put(standardKey.get(j), standardRow.getCell(j).getStringCellValue());
                }
                //excel中的状态码和对应的字段进行解析 SEX_M:男转为SEX_M
                //修改对应字段的属性，比如2018/12/25修改为date型数据
                beanJson = reInitBeanJson(beanJson);
                StandardDetailVo standardDetail = new StandardDetailVo();
                //转换为对应的实体类
                standardDetail = beanJson.toJavaObject(StandardDetailVo.class);
                standardDetailMapper.saveAll(standardDetail);
            }

        } catch (Exception e) {
            log.error(String.format("parse excel exception!"), e);
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    log.error(String.format("parse excel exception!"), e);
                }
            }
        }
    }
*/

    /**
     * 将从excel中读取到的数据取出状态码，转换不同类型的数据
     *
     * @param beanJson 需要转换字段信息的json
     * @return 返回转换后的json
     * @throws ParseException string型数据转为其他类型数据错误
     */
    private static JSONObject reInitBeanJson(JSONObject beanJson) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
        //截取value里面的状态值
        String emplSex = StringUtils.isEmpty(beanJson.getString("emplSex")) ? null : subField(beanJson.getString("emplSex"));
        beanJson.put("emplSex", emplSex);
        String departId = StringUtils.isEmpty(beanJson.getString("departId")) ? null : subField(beanJson.getString("departId"));
        beanJson.put("departId", departId);
        //转换非string类型数据
        Date birthDay = StringUtils.isEmpty(beanJson.getString("birthDay")) ? null : sdf.parse(beanJson.getString("birthDay"));
        beanJson.put("birthDay", birthDay);

        Date inductionDate = StringUtils.isEmpty(beanJson.getString("inductionDate")) ? null : sdf.parse(beanJson.getString("inductionDate"));
        beanJson.put("inductionDate", inductionDate);
        BigDecimal emplSalary = StringUtils.isEmpty(beanJson.getString("emplSalary")) ? null : BigDecimal.valueOf(Double.parseDouble(beanJson.getString("emplSalary")));
        beanJson.put("emplSalary", emplSalary);
        return beanJson;

    }

    private static String subField(String field) {
        String fieldCode = null;
        if (!StringUtils.isEmpty(field)) {
            String[] strArr = field.split(":");
            fieldCode = strArr[0];
        }
        return fieldCode;
    }


    private Set<String> getParticipleByES(String content) throws IOException {
        LinkedHashSet<String> strings = new LinkedHashSet<>();
        Request request = new Request("GET", "_analyze");
        JSONObject entity = new JSONObject();
        entity.put("analyzer", "ik_smart");
        entity.put("text", content);
        request.setJsonEntity(entity.toJSONString());
        Response response = this.client.getLowLevelClient().performRequest(request);
        JSONObject tokens = JSONObject.parseObject(EntityUtils.toString(response.getEntity()));
        JSONArray arrays = tokens.getJSONArray("tokens");
        for (int i = 0; i < arrays.size(); i++) {
            JSONObject obj = JSON.parseObject(arrays.getString(i));
            String word = obj.getString("token");
            if (word.length() > 1) {
                strings.add(word);
            }
        }
        return strings;
    }


    private List<StandardVo> searchAssociationStandByKeys(Set<String> key, Long id) {
        LinkedHashSet<StandardVo> list = new LinkedHashSet<>();
        //拼接es搜索语句
        for (String s : key) {
            extracted(id, list, s);
        }
        List<StandardVo> collect = list.stream().collect(Collectors.toList());
        log.info(collect.toString() + "44444444444444444444");
        return collect;
    }

    private void extracted(Long id, LinkedHashSet<StandardVo> list, String s) {
        SearchSourceBuilder searchSourceBuilder = buildSearchSource(s);
        //创建并设置SearchRequest对象
        SearchRequest searchRequest = new SearchRequest();
        //设置request要搜索的索引和类型
        searchRequest.indices("stand_title");
        //设置SearchSourceBuilder查询属性
        searchRequest.source(searchSourceBuilder);
        SearchResponse searchResponse = null;
        try {
            searchResponse = client.search(searchRequest, COMMON_OPTIONS);
        } catch (IOException e) {
            e.printStackTrace();
        }
        assert searchResponse != null;
        SearchHit[] hits = searchResponse.getHits().getHits();
        Arrays.stream(hits).forEach(hit -> {
            Map<String, Object> sourceAsMap = hit.getSourceAsMap();
            StandardVo standardVo = BeanUtil.mapToBeanIgnoreCase(sourceAsMap, StandardVo.class, true);
            standardVo.setExecuteUnit(sourceAsMap.containsKey("executeunit") ? sourceAsMap.get("executeunit").toString() : "");
            if (!id.equals(standardVo.getId())) {
                list.add(standardVo);
            }
        });
    }


    /**
     * 拼接es搜索语句
     *
     * @return
     */
    private SearchSourceBuilder buildSearchSource(String s) {
        SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
        //定义query查询
        BoolQueryBuilder builder = QueryBuilders.boolQuery();
        //过滤失效数据：logstash每分钟同步数据库,通过同步时间筛选,过滤失效数据
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Calendar beforeTime = Calendar.getInstance();
        beforeTime.add(Calendar.MINUTE, -3);
        // builder.must(QueryBuilders.rangeQuery("es_update_time").gte(sdf.format(beforeTime.getTime())).lte(sdf.format(new Date())).format("yyyy-MM-dd HH:mm:ss"));
        if (!"".equals(s)) {
            //分词检索
            //分词检索  //标题
            builder.should(QueryBuilders.matchQuery("tile", s))
                    //副标题
                    .should(QueryBuilders.matchQuery("subtitle", s))
                    // 标号
                    .should(QueryBuilders.matchQuery("num", s))
                    //归口单位
                    .should(QueryBuilders.matchQuery("unit", s))
                    //执行单位
                    .should(QueryBuilders.matchQuery("executeunit", s))
                    // 主管部门
                    .should(QueryBuilders.matchQuery("deft", s))
                    // 起草单位
                    .should(QueryBuilders.matchQuery("writeunit", s))
                    // 起草人
                    .should(QueryBuilders.matchQuery("author", s))
                    //标准简介，
                    .should(QueryBuilders.matchQuery("content", s))
                    // 标签
                    .should(QueryBuilders.matchQuery("tags", s))
                    //查询数据是否展示必须是1的
                    .must(QueryBuilders.matchQuery("isshow", 1))
                    //查询数据是否删除必须是1的
                    .must(QueryBuilders.matchQuery("deleted", 1));
        }
        log.info("ES检索命令 BoolQueryBuilder={}", builder.toString());
        searchSourceBuilder.query(builder);
        log.info("ES检索命令 SearchSourceBuilder={}", searchSourceBuilder.toString());
        return searchSourceBuilder;
    }

    @Override
    public List<Map> picChart() {
        return standardMapper.picChart();
    }

    @Override
    public List<Map> cylindricalChart() {
        return standardMapper.cylindricalChart();
    }
}
